﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Security.Cryptography;
using Analysis.Entities;
using Dapper;
using System.Data;

namespace Analysis.DAL.Repositories
{
    public class SetupRepository : BaseRepository
    {
        public IEnumerable<Depart> FindDepart()
        {
            string strSQL = @"  SELECT d.DepartId, d.DepartName, d.IsAdmin, d.OrderNumber, d.APColumnName,
                        convert(bit, case when exists(select 1 from [User] (nolock) as u where d.DepartId = u.DepartId) then 0 else 1 end) as CanDelete
                    from [Depart] (nolock) as d
                    order by d.OrderNumber";
            return Connection.Query<Depart>(strSQL);
        }

        public Depart FindDepartByColumnName(string apColumnName)
        {
            string strSQL = @"  SELECT d.DepartId, d.DepartName, d.IsAdmin, d.OrderNumber, d.APColumnName
                    from [Depart] (nolock) as d
                    where d.APColumnName = @APColumnName";
            return Connection.Query<Depart>(strSQL, new { APColumnName = apColumnName }).SingleOrDefault();
        }

        public string EditDepart(string departId, string departName)
        {
            var p = new DynamicParameters();
            p.Add("DepartId", departId);
            p.Add("DepartName", departName);
            p.Add("MsgErr", dbType: DbType.String, direction: ParameterDirection.Output, size: 100);
            Connection.Execute(@"spDepartEdit @DepartId = @DepartId, @DepartName = @DepartName, @MsgErr = @MsgErr out", p);

            return p.Get<string>("MsgErr");
        }

        public void AddDepart()
        {
            Connection.Execute(@"spDepartAdd");
        }

        public void UpDepart(int departId)
        {
            Connection.Execute(@"spDepartUp @DepartId", new { DepartId = departId });
        }

        public void DownDepart(int departId)
        {
            Connection.Execute(@"spDepartDown @DepartId", new { DepartId = departId });
        }

        public void DeleteDepart(int departId)
        {
            Connection.Execute(@"delete from Depart where DepartId = @DepartId", new { DepartId = departId });
        }

        public IEnumerable<SaleChannel> FindSaleChannel()
        {
            string strSQL = @"  SELECT d.SaleChannelId, d.SaleChannelName
                    from [SaleChannel] (nolock) as d";
            return Connection.Query<SaleChannel>(strSQL);
        }

        public SaleChannel FindSaleChannel(string saleChannelName)
        {
            string strSQL = @"  SELECT d.SaleChannelId, d.SaleChannelName
                    from [SaleChannel] (nolock) as d 
                    where d.SaleChannelName = @SaleChannelName";
            return Connection.Query<SaleChannel>(strSQL, new { SaleChannelName = saleChannelName }).SingleOrDefault();
        }

        public string EditSaleChannel(string SaleChannelId, string SaleChannelName)
        {
            var p = new DynamicParameters();
            p.Add("SaleChannelId", SaleChannelId);
            p.Add("SaleChannelName", SaleChannelName);
            p.Add("MsgErr", dbType: DbType.String, direction: ParameterDirection.Output, size: 100);
            Connection.Execute(@"spSaleChannelEdit @SaleChannelId = @SaleChannelId, @SaleChannelName = @SaleChannelName, @MsgErr = @MsgErr out", p);

            return p.Get<string>("MsgErr");
        }

        public void AddSaleChannel()
        {
            Connection.Execute(@"spSaleChannelAdd");
        }

        public void DeleteSaleChannel(int saleChannelId)
        {
            Connection.Execute(@"delete from SaleChannel where SaleChannelId = @SaleChannelId", new { SaleChannelId = saleChannelId });
        }

        public IEnumerable<SaleChannel> SaleChannelCombo(bool includeAll, bool includeEmpty)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as SaleChannelId, '(Все)' as SaleChannelName, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as SaleChannelId, '(Выберите канал)' as SaleChannelName, 0 as SortIndex union all ";
            strSQL += @"select SaleChannelId, SaleChannelName, 1 as SortIndex from [SaleChannel] (nolock) order by SortIndex, SaleChannelName";
            return Connection.Query<SaleChannel>(strSQL);
        }
        
        public IEnumerable<StorePoint> FindStorePoint()
        {
            string strSQL = @"  SELECT d.StorePointId, d.StorePointName
                    from [StorePoint] (nolock) as d";
            return Connection.Query<StorePoint>(strSQL);
        }

        public StorePoint FindStorePoint(string storePointName)
        {
            string strSQL = @"  SELECT d.StorePointId, d.StorePointName
                    from [StorePoint] (nolock) as d
                    where d.StorePointName = @StorePointName";
            return Connection.Query<StorePoint>(strSQL, new { StorePointName = storePointName }).SingleOrDefault();
        }

        public string EditStorePoint(string StorePointId, string StorePointName)
        {
            var p = new DynamicParameters();
            p.Add("StorePointId", StorePointId);
            p.Add("StorePointName", StorePointName);
            p.Add("MsgErr", dbType: DbType.String, direction: ParameterDirection.Output, size: 100);
            Connection.Execute(@"spStorePointEdit @StorePointId = @StorePointId, @StorePointName = @StorePointName, @MsgErr = @MsgErr out", p);

            return p.Get<string>("MsgErr");
        }

        public void AddStorePoint()
        {
            Connection.Execute(@"spStorePointAdd");
        }

        public void DeleteStorePoint(int StorePointId)
        {
            Connection.Execute(@"delete from StorePoint where StorePointId = @StorePointId", new { StorePointId = StorePointId });
        }

        public IEnumerable<StorePoint> StorePointCombo(bool includeAll, bool includeEmpty)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as StorePointId, '(Все)' as StorePointName, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as StorePointId, '(Выберите формат)' as StorePointName, 0 as SortIndex union all ";
            strSQL += @"select StorePointId, StorePointName, 1 as SortIndex from [StorePoint] (nolock) order by SortIndex, StorePointName";
            return Connection.Query<StorePoint>(strSQL);
        }

        public Depart FindDepartById(int departId)
        {
            string strSQL = @"  SELECT d.*
                    from [Depart] (nolock) as d
                    where d.DepartId = " + departId;
            return Connection.Query<Depart>(strSQL).SingleOrDefault();
        }
    }
}
